﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace DataBase
{
    
    public partial class Default : System.Web.UI.Page
    {
        public SqlConnection GetConnection()
        {
            string conStr = "Data Source=LAPTOP-PSIKN2LA;Initial Catalog=db_LibraryMS;Integrated Security=True";
            SqlConnection conn = new SqlConnection(conStr);
            return conn;
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGridView();
            }
        }
        private void BindGridView()
        {
            using (SqlConnection conn = GetConnection())
            {
                string sql = "SELECT id, name, pwd FROM tb_reader";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                DetailsView1.DataSource = dt;
                DetailsView1.DataBind();
                // 添加到DetailsView1中
                DetailsView1.Fields.Add(new BoundField() { DataField = "id", HeaderText = "ID" });
                DetailsView1.Fields.Add(new BoundField() { DataField = "name", HeaderText = "Name" });
                DetailsView1.Fields.Add(new BoundField() { DataField = "pwd", HeaderText = "Password" });
            }
        
        }
        protected void BindDataToDetailsView()
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                string query = "SELECT id, name, pwd FROM tb_reader";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    DetailsView1.DataSource = table;
                    DetailsView1.DataBind();
                }
            }
        }
        protected void InsertData()
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                string query = "INSERT INTO tb_reader (id, name, pwd) VALUES (@id, @name, @pwd)";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", TextBox1.Text);
                    command.Parameters.AddWithValue("@name", TextBox2.Text);
                    command.Parameters.AddWithValue("@pwd", TextBox3.Text);
                    command.ExecuteNonQuery();
                }
            }
            BindDataToDetailsView();
        }
        protected void UpdateData()
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                string query = "UPDATE tb_reader SET name = @name, pwd = @pwd WHERE id = @id";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", TextBox1.Text);
                    command.Parameters.AddWithValue("@name", TextBox2.Text);
                    command.Parameters.AddWithValue("@pwd", TextBox3.Text);
                    command.ExecuteNonQuery();
                }
            }
            BindDataToDetailsView();
        }

        protected void DeleteData()
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Open();
                string query = "DELETE FROM tb_reader WHERE id = @id";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", TextBox1.Text);
                    command.ExecuteNonQuery();
                }
            }
            BindDataToDetailsView();
        }
        protected void DetailsView1_PageIndexChanging(object sender, DetailsViewPageEventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            InsertData();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            DeleteData();
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            UpdateData();
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            BindGridView();
        }
    }
}